"""
食物热量参照表 DAO 层（数据访问对象）
负责 food_cal 表的增删改查，包含关联 food_cate 表查询类别名称的逻辑
"""
from app.db import db
from app.Models.HealthModels.FoodModels.FoodCalModel import FoodCal  # 假设模型在 app/models/food_cal.py
from app.Models.HealthModels.FoodModels.FoodCateModel import FoodCate  # 关联食物类别模型
from sqlalchemy.exc import IntegrityError, SQLAlchemyError
from sqlalchemy import join


class FoodCalDAO:
    """食物热量 DAO 类，封装增删改查及关联查询方法"""

    @staticmethod
    def add_food_cal(food_cate_id, food_name, kcal_per_piece, gram_per_piece):
        """
        新增食物热量记录
        :param food_cate_id: 食物类别ID（关联 food_cate.id）
        :param food_name: 食物名称（必填，唯一）
        :param kcal_per_piece: 每份热量（kcal，必填）
        :param gram_per_piece: 每份克重（g，必填）
        :return: 新增的 FoodCal 实例（含 id）
        :raises: IntegrityError - 名称重复/类别ID不存在；SQLAlchemyError - 数据库异常
        """
        try:
            # 校验关联的类别ID是否存在（可选，也可依赖数据库外键约束）
            cate_exists = FoodCate.query.filter_by(id=food_cate_id).first()
            if not cate_exists:
                raise ValueError(f"关联的食物类别ID {food_cate_id} 不存在")

            new_food = FoodCal(
                food_cate_id=food_cate_id,
                food_name=food_name.strip(),
                kcal_per_piece=kcal_per_piece,
                gram_per_piece=gram_per_piece
            )
            db.session.add(new_food)
            db.session.commit()
            return new_food
        except ValueError as e:
            db.session.rollback()
            raise ValueError(str(e))
        except IntegrityError as e:
            db.session.rollback()
            if "unique constraint" in str(e.orig).lower():
                raise IntegrityError(f"食物名称 '{food_name}' 已存在（名称需唯一）", orig=e.orig)
            else:
                raise IntegrityError(f"新增失败：{str(e.orig)}", orig=e.orig)
        except SQLAlchemyError as e:
            db.session.rollback()
            raise SQLAlchemyError(f"新增食物热量记录失败：{str(e)}")

    @staticmethod
    def _join_food_cate_query():
        """
        内部辅助方法：构建 food_cal 与 food_cate 的关联查询（JOIN）
        :return: Query 对象（已关联两张表）
        """
        # JOIN 两张表，关联条件为 food_cal.food_cate_id = food_cate.id
        return db.session.query(
            FoodCal,  # 食物热量表所有字段
            FoodCate.food_cate_name  # 关联查询类别名称
        ).join(FoodCate, FoodCal.food_cate_id == FoodCate.id)

    @staticmethod
    def get_food_cal_by_id(food_id):
        """
        根据 ID 查询食物热量记录（含对应的食物类别名称）
        :param food_id: 食物记录 ID
        :return: 字典（含食物信息+类别名称），不存在则返回 None
        :raises: SQLAlchemyError - 数据库异常
        """
        try:
            # 关联查询
            result = FoodCalDAO._join_food_cate_query().filter(FoodCal.id == food_id).first()
            if not result:
                return None

            food_cal, food_cate_name = result
            # 组合结果：食物信息 + 类别名称
            food_dict = food_cal.to_dict()
            food_dict["food_cate_name"] = food_cate_name  # 新增类别名称字段
            return food_dict
        except SQLAlchemyError as e:
            raise SQLAlchemyError(f"查询食物热量（ID: {food_id}）失败：{str(e)}")

    @staticmethod
    def get_food_cal_by_name(food_name):
        """
        根据名称查询食物热量记录（精确匹配，含类别名称）
        :param food_name: 食物名称
        :return: 字典（含食物信息+类别名称），不存在则返回 None
        :raises: SQLAlchemyError - 数据库异常
        """
        try:
            result = FoodCalDAO._join_food_cate_query().filter(
                FoodCal.food_name == food_name.strip()
            ).first()
            if not result:
                return None

            food_cal, food_cate_name = result
            food_dict = food_cal.to_dict()
            food_dict["food_cate_name"] = food_cate_name
            return food_dict
        except SQLAlchemyError as e:
            raise SQLAlchemyError(f"查询食物热量（名称: {food_name}）失败：{str(e)}")

    @staticmethod
    def get_all_food_cals():
        """
        查询所有食物热量记录（含类别名称，按 ID 升序排列）
        :return: 字典列表（每条记录含食物信息+类别名称）
        :raises: SQLAlchemyError - 数据库异常
        """
        try:
            results = FoodCalDAO._join_food_cate_query().order_by(FoodCal.id.asc()).all()
            # 转换为字典列表
            return [
                {**food_cal.to_dict(), "food_cate_name": food_cate_name}
                for food_cal, food_cate_name in results
            ]
        except SQLAlchemyError as e:
            raise SQLAlchemyError(f"查询所有食物热量记录失败：{str(e)}")

    @staticmethod
    def get_food_cals_by_cate_id(food_cate_id):
        """
        新增方法：根据食物类别ID，查询该类别下的所有食物记录（含类别名称）
        :param food_cate_id: 食物类别ID
        :return: 字典列表（每条记录含食物信息+类别名称），无结果返回空列表
        :raises: SQLAlchemyError - 数据库异常
        """
        try:
            # 先校验类别ID是否存在（可选）
            cate_exists = FoodCate.query.filter_by(id=food_cate_id).first()
            if not cate_exists:
                return []  # 类别不存在则返回空列表

            results = FoodCalDAO._join_food_cate_query().filter(
                FoodCal.food_cate_id == food_cate_id
            ).order_by(FoodCal.id.asc()).all()

            return [
                {**food_cal.to_dict(), "food_cate_name": food_cate_name}
                for food_cal, food_cate_name in results
            ]
        except SQLAlchemyError as e:
            raise SQLAlchemyError(f"查询类别ID {food_cate_id} 对应的食物记录失败：{str(e)}")

    @staticmethod
    def update_food_cal(food_id, **kwargs):
        """
        根据 ID 更新食物热量记录
        :param food_id: 要更新的食物记录 ID
        :param kwargs: 要更新的字段（可选：food_cate_id、food_name、kcal_per_piece、gram_per_piece）
        :return: 更新后的字典（含食物信息+类别名称），不存在则返回 None
        :raises: IntegrityError - 名称重复/类别ID不存在；SQLAlchemyError - 数据库异常
        """
        try:
            # 先查询原记录是否存在
            food_cal = FoodCal.query.filter_by(id=food_id).first()
            if not food_cal:
                return None

            # 处理更新字段
            if "food_cate_id" in kwargs:
                new_cate_id = kwargs["food_cate_id"]
                # 校验新类别ID是否存在
                if not FoodCate.query.filter_by(id=new_cate_id).first():
                    raise ValueError(f"更新失败：关联的食物类别ID {new_cate_id} 不存在")
                food_cal.food_cate_id = new_cate_id

            if "food_name" in kwargs:
                new_name = kwargs["food_name"].strip()
                food_cal.food_name = new_name

            if "kcal_per_piece" in kwargs:
                food_cal.kcal_per_piece = kwargs["kcal_per_piece"]

            if "gram_per_piece" in kwargs:
                food_cal.gram_per_piece = kwargs["gram_per_piece"]

            db.session.commit()
            # 返回更新后的完整信息（含类别名称）
            return FoodCalDAO.get_food_cal_by_id(food_id)
        except ValueError as e:
            db.session.rollback()
            raise ValueError(str(e))
        except IntegrityError as e:
            db.session.rollback()
            if "unique constraint" in str(e.orig).lower():
                raise IntegrityError(f"更新失败：食物名称 '{kwargs.get('food_name')}' 已存在", orig=e.orig)
            else:
                raise IntegrityError(f"更新失败：{str(e.orig)}", orig=e.orig)
        except SQLAlchemyError as e:
            db.session.rollback()
            raise SQLAlchemyError(f"更新食物热量（ID: {food_id}）失败：{str(e)}")

    @staticmethod
    def delete_food_cal(food_id):
        """
        根据 ID 删除食物热量记录
        :param food_id: 要删除的食物记录 ID
        :return: bool - 删除成功返回 True，不存在返回 False
        :raises: SQLAlchemyError - 数据库异常
        """
        try:
            food_cal = FoodCal.query.filter_by(id=food_id).first()
            if not food_cal:
                return False

            db.session.delete(food_cal)
            db.session.commit()
            return True
        except SQLAlchemyError as e:
            db.session.rollback()
            raise SQLAlchemyError(f"删除食物热量（ID: {food_id}）失败：{str(e)}")

    @staticmethod
    def search_food_cals_by_keyword(keyword):
        """
        按关键词模糊查询食物记录（匹配食物名称，含类别名称）
        :param keyword: 搜索关键词
        :return: 字典列表（每条记录含食物信息+类别名称），无结果返回空列表
        :raises: SQLAlchemyError - 数据库异常
        """
        try:
            if not keyword or keyword.strip() == "":
                return []

            results = FoodCalDAO._join_food_cate_query().filter(
                FoodCal.food_name.ilike(f"%{keyword.strip()}%")
            ).order_by(FoodCal.id.asc()).all()

            return [
                {**food_cal.to_dict(), "food_cate_name": food_cate_name}
                for food_cal, food_cate_name in results
            ]
        except SQLAlchemyError as e:
            raise SQLAlchemyError(f"模糊查询食物记录（关键词: {keyword}）失败：{str(e)}")
